{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 安装pymysql\n",
    "- 通过pip命令进行第三包pymysql的安装"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting PyMySQL\n",
      "  Downloading https://files.pythonhosted.org/packages/1a/ea/dd9c81e2d85efd03cfbf808736dd055bd9ea1a78aea9968888b1055c3263/PyMySQL-0.10.1-py2.py3-none-any.whl (47kB)\n",
      "Installing collected packages: PyMySQL\n",
      "Successfully installed PyMySQL-0.10.1\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "You are using pip version 10.0.1, however version 20.3b1 is available.\n",
      "You should consider upgrading via the 'python -m pip install --upgrade pip' command.\n"
     ]
    }
   ],
   "source": [
    "!pip install PyMySQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 通过import pymysql引入包"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 实验一 查询users表内容并打印输出\n",
    "- 获得blog数据库连接\n",
    "- 查询users表的所有记录\n",
    "- users表结构如下\n",
    "\n",
    "id|name|password\n",
    "--|--|--|\n",
    "1|张三|123456\n",
    "2|李四|abcd\n",
    "3|jack|abcdef\n",
    "4|rose|67890\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#获得数据库连接\n",
    "connection = pymysql.connect(\"localhost\",\"root\",\"123456\",\"blog\")\n",
    "# 使用cursor()方法获取操作游标 \n",
    "cursor = connection.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id=1,name=张三,password=123456\n",
      "\n",
      "id=2,name=李四,password=abcd\n",
      "\n",
      "id=4,name=rose,password=67890\n",
      "\n",
      "id=5,name=jams,password=hijk\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# SQL 查询语句\n",
    "sql = \"SELECT * FROM users\"\n",
    "try:\n",
    "    # 执行SQL语句\n",
    "    cursor.execute(sql)\n",
    "    # 获取所有记录列表\n",
    "    results = cursor.fetchall()\n",
    "    for row in results:    \n",
    "        id = row[0]\n",
    "        name = row[1]\n",
    "        password = row[2]\n",
    "        # 打印结果\n",
    "        print (\"id=%d,name=%s,password=%s\\n\" % (id, name,password))\n",
    "except:\n",
    "    print (\"Error: unable to fetch data\")\n",
    "finally:\n",
    "   #关闭数据库连接\n",
    "    connection.close() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 实验二 插入数据\n",
    "- 获得blog数据库连接\n",
    "- 插入一条记录 jams，hijk"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#获得数据库连接\n",
    "connection = pymysql.connect(\"localhost\",\"root\",\"123456\",\"blog\")\n",
    "# 使用cursor()方法获取操作游标 \n",
    "cursor = connection.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# SQL 插入语句\n",
    "sql = \"INSERT INTO users(name,password) VALUES ('jams', 'hijk')\"\n",
    "try:\n",
    "    # 执行SQL语句\n",
    "    cursor.execute(sql)\n",
    "    # 提交到数据库执行\n",
    "    connection.commit()\n",
    "except:\n",
    "    print (\"Error: unable to insert data\")\n",
    "    # 发生错误时回滚\n",
    "    connection.rollback()\n",
    "finally:\n",
    "    #关闭数据库连接\n",
    "    connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 实验三 更新数据\n",
    "- 获得blog数据库连接\n",
    "- 将users表中name为jack的密码更改为67890"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#获得数据库连接\n",
    "connection = pymysql.connect(\"localhost\",\"root\",\"123456\",\"blog\")\n",
    "# 使用cursor()方法获取操作游标 \n",
    "cursor = connection.cursor()\n",
    "# SQL 更新语句\n",
    "sql = \"UPDATE users SET password = '67890' WHERE name='jack'\"\n",
    "try:\n",
    "    # 执行SQL语句\n",
    "    cursor.execute(sql)\n",
    "    # 提交到数据库执行\n",
    "    connection.commit()\n",
    "except:\n",
    "    print (\"Error: unable to upate data\")\n",
    "    # 发生错误时回滚\n",
    "    connection.rollback()\n",
    "finally:\n",
    "    #关闭数据库连接\n",
    "    connection.close()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 实验四 删除数据\n",
    "- 获得blog数据库连接\n",
    "- 将users表中id为5的记录删除"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#获得数据库连接\n",
    "connection = pymysql.connect(\"localhost\",\"root\",\"123456\",\"blog\")\n",
    "# 使用cursor()方法获取操作游标 \n",
    "cursor = connection.cursor()\n",
    "# SQL 删除语句\n",
    "sql = \"delete from users where id=5\"\n",
    "try:\n",
    "    # 执行SQL语句\n",
    "    cursor.execute(sql)\n",
    "    # 提交到数据库执行\n",
    "    connection.commit()\n",
    "except:\n",
    "    print (\"Error: unable to delete data\")\n",
    "    # 发生错误时回滚\n",
    "    connection.rollback()\n",
    "finally:\n",
    "    #关闭数据库连接\n",
    "    connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
